import pandas as pd

from db_conn import pd_conn_zfw, pd_conn_wfw
from datetime import datetime


#
def parts(startTime, endTime, province, wareUser):

    if wareUser is not None and wareUser != '':
        sql = f"""
            select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
            where f_ware_house_code is not null and f_status = 0 and f_ware_house_name = '{wareUser}'
        """
        df = pd_conn_zfw(sql)
        if df.shape[0] == 0:
            wareUser = ''

    # 配件下单明细
    def parts_order_detail():

        if province is None or province == '':
            where_province = ''
        else:
            sql1 = f'''
                select code pcode, name 省 from area_code_2019
                where level = 2
            '''
            df1 = pd_conn_zfw(sql1)
            df_pcode = df1.loc[df1['省'] == province, 'pcode']
            pcode = df_pcode.iloc[0]
            where_province = f"""and a.f_province_code = {pcode}"""

        if wareUser is None or wareUser == '':
            where_wareUser = ''
        else:
            where_wareUser = f"""AND i.f_ware_house_name like '%{wareUser}%'"""
        sql2 = f'''
            SELECT 
                o.f_create_time 下单时间,
                o.f_user_id uid,
                i.f_part_code 配件id,
                i.f_part_name 配件名称,
                g.f_part_specification 规格,
                i.f_unit 单位,
                i.f_num 数量,
                i.f_price 价格,
                if(
                    instr(i.f_ware_house_name,  '(') >=1,
                    left(i.f_ware_house_name, instr(i.f_ware_house_name,  '(')-1),
                    i.f_ware_house_name) 分仓合伙人,
                # i.f_ware_house_name ,
                a.f_receiver_name 收货人姓名,
                a.f_province '省',
                a.f_city '市',
                a.f_county '区',
                a.f_address 收货地址,
                a.f_receiver_phone 联系电话,
                a.f_province_code+0 pcode
            from 
                t_part_order o
                left join t_part_order_item i on i.f_part_order_id=o.f_id
                left join t_part_good g on g.f_part_code=i.f_part_code
                left join t_address a on a.f_id=o.f_address_id
            where 
                o.f_create_time >= '{startTime}'
                and o.f_create_time < '{endTime}'
                {where_province}
                {where_wareUser}
        '''
        df2 = pd_conn_wfw(sql2, 'ygl_stock')

        sql3 = '''
            select f_id uid,f_username 下单人 from t_factory_user where f_status = 0
        '''
        df3 = pd_conn_zfw(sql3)
        df_m = df2.merge(df3, on='uid', how='left')
        df_parts_order_detail = df_m[['下单时间', '下单人', '配件id', '配件名称', '规格', '单位', '数量', '价格', '分仓合伙人',
                                     '收货人姓名', '省', '市', '区', '收货地址', '联系电话']]

        return df_parts_order_detail

    # 工单导出
    def work_order_export():
        sql3 = f"""
            select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
            where f_ware_house_code is not null and f_status = 0
        """
        df_ware = pd_conn_zfw(sql3)

        if province is None or province == '':
            where_province = ''
        else:
            where_province = f"""and f.f_province = '{province}'"""

        if wareUser is None or wareUser == '':
            where_wareUser = ''
        else:
            df_mobile = df_ware.loc[df_ware['分仓合伙人'].str.contains(wareUser), '分仓手机号']
            mobile = df_mobile.iloc[0]
            where_wareUser = f"""and r.f_mobile = '{mobile}'"""

        sql1 = f"""
            SELECT   
                r.f_task_id 工单id,r.f_mobile 分仓手机号,
                r.f_part_code 配件id,r.f_part_name 配件名称,g.f_part_price 配件价格
            FROM 
                t_stock_record r
                left join t_part_good g on g.f_part_code=r.f_part_code
            where r.f_type = 2 and r.f_task_id is not null
            {where_wareUser}
        """

        sql2 = f"""
            SELECT 
                f.f_id 站点id,f.f_name 站点名称,f.f_province 省,f.f_city 市,f.f_district 区,
                case 
                    when f.f_station_num in (1,2,3,4,5,6,6001,6002) then '愿景'
                    when f.f_station_num in (6003, 6004) then '愿景LITE'
                    when f.f_station_num = 10 then '阿尔法'
                    when f.f_station_num = 8001 then '隧道机'
                    else '其他外部机'
                end 机型,
                IF (f.f_no LIKE '%6%B%', '二号工厂' ,IF(f.f_no like '%06%C%', '三号工厂', '一号工厂')) AS 工厂,
                bs.f_finished_time 落站时间,f.f_work_start_time 开业时间,
                t.f_id 工单id,
                case t.f_type 
                    when 1 then '普通任务'
                    when 2 then '事故类'
                    when 3 then '报警自动生成' 
                    when 4 then '订单评价'
                    when 5 then '维护工单' 
                    when 6 then '保养工单'
                    when 7 then '反馈技术部工单' 
                    when 8 then '客需反馈、洁净问题' 
                    when 9 then '星任务' 
                    when 10 then '改造' 
                    when 11 then '整改' 
                    when 12 then '客诉' 
                    when 13 then '排查工单' 
                    when 14 then 'C端差评工单'
                    else t.f_type
                end 工单类型,
                t.f_content 工单内容,
                t.f_create_user_name 创建人,
                t.f_user_names 执行人,
                t.f_create_time 创建时间,
                t.f_complete_time 完成时间
            FROM 
                t_task t
                join t_factory f on f.f_id=t.f_factoryIds
                left join t_factory_build_step bs on bs.f_factory_id=f.f_id and bs.f_task_code in (6110, 558, 5090)
            WHERE 
                f.f_status = 0
                and t.f_create_time >= '{startTime}'
                and t.f_create_time < '{endTime}'
                {where_province}
        """

        df_parts = pd_conn_wfw(sql1, 'ygl_stock')
        df_wo = pd_conn_zfw(sql2)

        df_m = df_wo.merge(df_parts, how='inner', on='工单id')
        df_total = df_m.merge(df_ware, how='left', on='分仓手机号')
        df_work_order_export = df_total[[
            '工单id', '站点id', '站点名称', '省', '市', '区', '机型', '工厂', '落站时间', '开业时间', '工单类型', '工单内容',
            '创建人', '创建时间', '分仓合伙人', '分仓手机号', '执行人', '完成时间', '配件id', '配件名称', '配件价格']]

        return df_work_order_export

    # 配件库存明细
    def parts_stork_detail():

        if wareUser is None or wareUser == '':
            where_wareUser = ''
            sql1 = f"""
                select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
                where f_ware_house_code is not null and f_status = 0
            """
            df1 = pd_conn_zfw(sql1)
        else:
            sql1 = f"""
                select f_ware_house_code 分仓手机号, f_ware_house_name 分仓合伙人 from t_factory_user 
                where f_ware_house_code is not null and f_status = 0 and f_ware_house_name = '{wareUser}'
            """
            df1 = pd_conn_zfw(sql1)

            df_mobile = df1.loc[df1['分仓合伙人'] == wareUser, '分仓手机号']
            mobile = df_mobile.iloc[0]
            where_wareUser = f"""and s.f_mobile = '{mobile}'"""

        if endTime is None or endTime == '':
            table = 't_user_stock'
            where_date = ''
            date = str(datetime.now())
        else:
            table = 't_user_stock_history'
            where_date = f"""and s.f_date = '{endTime}'"""
            date = endTime

        sql2 = f'''
            SELECT
                left('{date}',19) 日期,s.f_part_code 配件id,s.f_part_name 配件名称,g.f_part_specification 规格,g.f_part_Unit 单位,
                s.f_part_qty 库存数量,s.f_part_qty * g.f_part_price 库存金额,s.f_mobile 分仓手机号,s.f_update_time 更新时间
            FROM
                {table} s
                left join t_part_good g on g.f_part_code=s.f_part_code
            where
                1=1 -- 无意义，占个位置，方便后续代码
                {where_wareUser}
                {where_date}
        '''
        df2 = pd_conn_wfw(sql2, 'ygl_stock')
        df_m = df2.merge(df1, on='分仓手机号', how='left')
        df_parts_stork_detail = df_m[['日期', '配件id', '配件名称', '规格', '单位', '库存数量', '库存金额', '分仓手机号', '分仓合伙人', '更新时间']]

        return df_parts_stork_detail

    # 配件下单明细
    df_parts_order_detail = parts_order_detail()
    # 工单导出
    df_work_order_export = work_order_export()
    # 配件库存明细
    df_parts_stork_detail = parts_stork_detail()

    writer = pd.ExcelWriter('配件明细.xlsx')
    df_parts_order_detail.to_excel(writer, sheet_name='配件下单明细', index=False)
    df_work_order_export.to_excel(writer, sheet_name='工单导出', index=False)
    df_parts_stork_detail.to_excel(writer, sheet_name='配件库存明细', index=False)
    writer.save()
    writer.close()


if __name__ == '__main__':

    parts(startTime='2021-12-01', endTime='2022-01-01', province='', wareUser='')

